package dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import dao.BaseDao;
import dao.WaresDao;
import entity.Img;
import entity.News;
import entity.Wares;
import entity.WaresCar;
import entity.WaresType;

public class IWaresDao extends BaseDao implements WaresDao {
	Connection con = null;
	PreparedStatement ps = null;
	ResultSet rs = null;
	@Override
	public List<WaresType> FindType(String ttype) {//查询所有商品类型
		List<WaresType> list = new ArrayList<WaresType>();
		try {
			con = super.getConnection();
			String sql ="select * from shopping_type where 1=1 ";
			if (ttype!=""&&ttype!=null) {
				sql+=" and ttype = '"+ttype+"' ";
			}
			ps = con.prepareStatement(sql);
			rs=ps.executeQuery();
			while(rs.next()){
				WaresType wt = new WaresType();
				wt.setTid(rs.getInt(1));
				wt.setTtype(rs.getString(2));
				wt.setTtypelist(rs.getString(3));
				list.add(wt);
			}
			rs.close();
			ps.close();
			con.close();
			
			return list!=null?list:null;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	@Override
	public List<Wares> FindWares(int page, int pagesize, String type) {//根据类型查询商品
		List<Wares> list = new ArrayList<Wares>();
		try {
			con = super.getConnection();
			String sql ="select * from(select a.*, rownum rc from(select * from shopping_wares where 1=1 ";
			if (type!=""&&type!=null) {
				sql+=" and wtype = '"+type+"' ";
			}
			sql+=" order by wsellcount desc) a where rownum <= ?)  b where rc > ?";
			ps = con.prepareStatement(sql);

			ps.setInt(1, page*pagesize);
			ps.setInt(2, (page-1)*pagesize);
			rs=ps.executeQuery();
			while(rs.next()){
				Wares w= new Wares();
				w.setWid(rs.getLong(1));
				w.setUsername(rs.getString(2));
				w.setWname(rs.getString(3));
				w.setWprice(rs.getDouble(4));
				w.setWfare(rs.getDouble(5));
				w.setWdiscount(rs.getInt(6));
				w.setWstopdiscount(rs.getDate(7).toString());
				w.setWcount(rs.getLong(8));
				w.setWsellcount(rs.getLong(9));
				w.setWnew(rs.getString(10));
				w.setWtype(rs.getString(11));
				w.setWshow(rs.getLong(12));
				w.setWimgsrc(rs.getString(13));
				w.setWaddress(rs.getString(14));
				list.add(w);
			}
			rs.close();
			ps.close();
			con.close();
			
			return list!=null?list:null;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	public long FindWaresCount(int page, int pagesize, String type) {//根据类型查询商品总数
		long count=0;
		try {
			con = super.getConnection();
			String sql ="select count(*) from(select a.*, rownum rc from(select * from shopping_wares where 1=1 ";
			if (type!=""&&type!=null) {
				sql+=" and wtype = '"+type+"' ";
			}
			sql+=" order by wsellcount desc) a)";
			ps = con.prepareStatement(sql);
			rs=ps.executeQuery();
			while(rs.next()){
				count = rs.getLong(1);
			}
			rs.close();
			ps.close();
			con.close();
			
			return count!=0?count:0;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}
	@Override
	public Wares Findware(int id) {//根据ID 查询商品详细信息
		Wares w= new Wares();
		try {
			con = super.getConnection();
			String sql ="select * from shopping_wares where wid = ?";
			ps = con.prepareStatement(sql);
			ps.setInt(1, id);
			rs=ps.executeQuery();
			while(rs.next()){
				w.setWid(rs.getLong(1));
				w.setUsername(rs.getString(2));
				w.setWname(rs.getString(3));
				w.setWprice(rs.getDouble(4));
				w.setWfare(rs.getDouble(5));
				w.setWdiscount(rs.getInt(6));
				w.setWstopdiscount(rs.getDate(7).toString());
				w.setWcount(rs.getLong(8));
				w.setWsellcount(rs.getLong(9));
				w.setWnew(rs.getString(10));
				w.setWtype(rs.getString(11));
				w.setWshow(rs.getLong(12));
				w.setWimgsrc(rs.getString(13));
				w.setWaddress(rs.getString(14));
			}
			rs.close();
			ps.close();
			con.close();
			return w!=null?w:null;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	@Override
	public List<Img> Findwareimg(int id) {	//根据商品编号查询对应所有图片
		
		List<Img> list = new ArrayList<Img>();
		try {
			con = super.getConnection();
			String sql ="select * from shopping_img where wid = ? ";
			ps = con.prepareStatement(sql);
			ps.setInt(1, id);
			rs=ps.executeQuery();
			while(rs.next()){
				Img img = new Img();
				img.setId(rs.getInt(1));
				img.setWid(rs.getInt(2));
				img.setImgsrc(rs.getString(3));
				list.add(img);
			}
			rs.close();
			ps.close();
			con.close();
			
			return list!=null?list:null;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	
	@Override
	public boolean InputCart(int id,String username) {//根据商品ID 把商品放入购物车
		Wares w= new Wares();
		w=Findware(id);
		try {
			con = super.getConnection();
			String sql ="insert into shopping_car values(shopping_carseq.nextval,?,?,?,?,?,?,?,?)";
			ps = con.prepareStatement(sql);
			ps.setString(1, username);
			ps.setString(2, w.getUsername());
			ps.setLong(3, w.getWid());
			ps.setString(4, w.getWname());
			ps.setDouble(5, w.getWprice());
			ps.setInt(6,1);
			ps.setString(7, w.getWimgsrc());
			ps.setInt(8, w.getWdiscount());
			ps.execute();
			
			ps.close();
			con.close();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}
	
	
	@Override
	public List<WaresCar> ShowCar() {//查看购物车
		List<WaresCar> list = new ArrayList<WaresCar>();
		try {
			con = super.getConnection();
			String sql ="select * from shopping_car ";
			ps = con.prepareStatement(sql);
			rs=ps.executeQuery();
			while(rs.next()){
				WaresCar wc= new WaresCar();
				wc.setCid(rs.getLong(1));
				wc.setUsername1(rs.getString(2));
				wc.setUsername2(rs.getString(3));
				wc.setWid(rs.getLong(4));
				wc.setWname(rs.getString(5));
				wc.setWprice(rs.getDouble(6));
				wc.setWcount(rs.getInt(7));
				wc.setImgsrc(rs.getString(8));
				wc.setWdiscount(rs.getInt(9));
				list.add(wc);
			}
			rs.close();
			ps.close();
			con.close();
			
			return list!=null?list:null;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	@Override
	public boolean DeleteCarById(long id) {//根据购物车id删除对应物品
		try {
			con = super.getConnection();
			String sql ="delete shopping_car where cid = ?";
			ps = con.prepareStatement(sql);
			ps.setLong(1,id);
			ps.execute();
			
			ps.close();
			con.close();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}


	@Override
	public boolean PayCar(String username) {//根据用户名结算购物车
		List<WaresCar> list  = new ArrayList<WaresCar>();
		try {
			con = super.getConnection();
			String sql ="select * from shopping_car where  username1 = ?";
			ps = con.prepareStatement(sql);
			ps.setString(1, username);
			rs=ps.executeQuery();
			while(rs.next()){
				WaresCar wc= new WaresCar();
				wc.setCid(rs.getLong(1));
				wc.setUsername1(rs.getString(2));
				wc.setUsername2(rs.getString(3));
				wc.setWid(rs.getLong(4));
				wc.setWname(rs.getString(5));
				wc.setWprice(rs.getDouble(6)*rs.getInt(9)/100);//商品原价转成优惠价
				wc.setWcount(rs.getInt(7));
				wc.setImgsrc(rs.getString(8));
				list.add(wc);
			}
			rs.close();
			ps.close();
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		
		//把购物车的商品集合放入销售记录表中
		try {
			con = super.getConnection();
			String sql ="insert into shopping_list values(shopping_listseq.nextval,?,?,?,?,?,?,?,?)";
			ps = con.prepareStatement(sql);
			
			for (int i = 0; i < list.size(); i++) {
				WaresCar wc= new WaresCar();
				wc=list.get(i);
			ps.setString(1, wc.getUsername1());
			ps.setString(2, wc.getUsername2());
			ps.setLong(3, wc.getWid());
			ps.setString(4, wc.getWname());
			ps.setDouble(5, wc.getWprice());
			ps.setInt(6, wc.getWcount());
			ps.setString(7, wc.getImgsrc());
			ps.setString(8, new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date()));
			
			ps.execute();
			}
			rs.close();
			ps.close();
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		
		//修改商品信息 库存-1 出售+1
		try {
			con = super.getConnection();
			String sql ="update shopping_wares set wcount =wcount - ? ,wsellcount =wsellcount+ ? where wid=?";
			ps = con.prepareStatement(sql);
			for (int i = 0; i < list.size(); i++) {
				WaresCar wc= new WaresCar();
				wc=list.get(i);
				ps.setLong(1, wc.getWcount());
				ps.setLong(2, wc.getWcount());
				ps.setLong(3, wc.getWid());
				ps.execute();
			}
			rs.close();
			ps.close();
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		//清空购物车
		try {
			con = super.getConnection();
			String sql ="delete shopping_car where username1=?";
			ps = con.prepareStatement(sql);
			ps.setString(1, username);
			ps.execute();
			
			ps.close();
			con.close();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}
	
	
	@Override
	public List<Wares> FindHot() {//返回 销售量最多的9个商品
		List<Wares> list = new ArrayList<Wares>();
		try {
			con = super.getConnection();
			String sql ="select * from shopping_wares where rownum between 0 and 9 order by wsellcount desc";
			ps = con.prepareStatement(sql);
			rs=ps.executeQuery();
			while(rs.next()){
				Wares w= new Wares();
				w.setWid(rs.getLong(1));
				w.setUsername(rs.getString(2));
				w.setWname(rs.getString(3));
				w.setWprice(rs.getDouble(4));
				w.setWfare(rs.getDouble(5));
				w.setWdiscount(rs.getInt(6));
				w.setWstopdiscount(rs.getDate(7).toString());
				w.setWcount(rs.getLong(8));
				w.setWsellcount(rs.getLong(9));
				w.setWnew(rs.getString(10));
				w.setWtype(rs.getString(11));
				w.setWshow(rs.getLong(12));
				w.setWimgsrc(rs.getString(13));
				w.setWaddress(rs.getString(14));
				list.add(w);
			}
			rs.close();
			ps.close();
			con.close();
			return list!=null?list:null;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return null;
	}
	@Override
	public List<Wares> FindWaresByType(int page, int pagesize, String type) {
		List<Wares> list = new ArrayList<Wares>();
		try {
			con = super.getConnection();
			String sql ="select * from(select a.*, rownum rc from(select * from shopping_wares w,shopping_type t where w.wtype=t.ttypelist ";
			if (type!=""&&type!=null) {
				sql+=" and ttype = '"+type+"' ";
			}
			sql+=" order by wsellcount desc) a where rownum <= ?) b where rc > ?";
			ps = con.prepareStatement(sql);
			ps.setInt(1, page*pagesize);
			ps.setInt(2, (page-1)*pagesize);
			rs=ps.executeQuery();
			while(rs.next()){
				Wares w= new Wares();
				w.setWid(rs.getLong(1));
				w.setUsername(rs.getString(2));
				w.setWname(rs.getString(3));
				w.setWprice(rs.getDouble(4));
				w.setWfare(rs.getDouble(5));
				w.setWdiscount(rs.getInt(6));
				w.setWstopdiscount(rs.getDate(7).toString());
				w.setWcount(rs.getLong(8));
				w.setWsellcount(rs.getLong(9));
				w.setWnew(rs.getString(10));
				w.setWtype(rs.getString(11));
				w.setWshow(rs.getLong(12));
				w.setWimgsrc(rs.getString(13));
				w.setWaddress(rs.getString(14));
				list.add(w);
			}
			rs.close();
			ps.close();
			con.close();
			
			return list!=null?list:null;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	@Override
	public long FindWaresByTypeCount(int page, int pagesize, String type) {
		long count=0;
		try {
			con = super.getConnection();
			String sql ="select count(*) from(select a.*, rownum rc from(select * from shopping_wares w,shopping_type t where w.wtype=t.ttypelist ";
			if (type!=""&&type!=null) {
				sql+=" and ttype = '"+type+"' ";
			}
			sql+=" order by wsellcount desc) a)";
			ps = con.prepareStatement(sql);
			rs=ps.executeQuery();
			while(rs.next()){
				count = rs.getLong(1);
			}
			rs.close();
			ps.close();
			con.close();
			
			return count!=0?count:0;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}
	@Override
	public List<Wares> FindNew() {
		List<Wares> list = new ArrayList<Wares>();
		try {
			con = super.getConnection();
			String sql ="select * from shopping_wares where rownum between 0 and 4 order by WSTOPDISCOUNT desc";
			ps = con.prepareStatement(sql);
			rs=ps.executeQuery();
			while(rs.next()){
				Wares w= new Wares();
				w.setWid(rs.getLong(1));
				w.setUsername(rs.getString(2));
				w.setWname(rs.getString(3));
				w.setWprice(rs.getDouble(4));
				w.setWfare(rs.getDouble(5));
				w.setWdiscount(rs.getInt(6));
				w.setWstopdiscount(rs.getDate(7).toString());
				w.setWcount(rs.getLong(8));
				w.setWsellcount(rs.getLong(9));
				w.setWnew(rs.getString(10));
				w.setWtype(rs.getString(11));
				w.setWshow(rs.getLong(12));
				w.setWimgsrc(rs.getString(13));
				w.setWaddress(rs.getString(14));
				list.add(w);
			}
			rs.close();
			ps.close();
			con.close();
			return list!=null?list:null;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	@Override
	public List<Wares> SelectWares(int page,int pagesize,String word) {
		List<Wares> list = new ArrayList<Wares>();
		try {
			con = super.getConnection();
			
			String sql ="select * from(select a.*, rownum rc from(select * from shopping_wares w where 1=1 ";
			if (word!=""&&word!=null) {
				sql+=" and w.wname like '%"+word+"%' or w.wtype like '%"+word+"%' ";
			}
			sql+=" ) a where rownum <= ?) b where rc > ?";
			
			ps = con.prepareStatement(sql);
			ps.setInt(1, page*pagesize);
			ps.setInt(2, (page-1)*pagesize);
			rs=ps.executeQuery();
			while(rs.next()){
				Wares w= new Wares();
				w.setWid(rs.getLong(1));
				w.setUsername(rs.getString(2));
				w.setWname(rs.getString(3));
				w.setWprice(rs.getDouble(4));
				w.setWfare(rs.getDouble(5));
				w.setWdiscount(rs.getInt(6));
				w.setWstopdiscount(rs.getDate(7).toString());
				w.setWcount(rs.getLong(8));
				w.setWsellcount(rs.getLong(9));
				w.setWnew(rs.getString(10));
				w.setWtype(rs.getString(11));
				w.setWshow(rs.getLong(12));
				w.setWimgsrc(rs.getString(13));
				w.setWaddress(rs.getString(14));
				list.add(w);
			}
			rs.close();
			ps.close();
			con.close();
			return list!=null?list:null;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return null;
	}
	@Override
	public long SelectWaresCount(String word) {
		long count=0;
		try {
			con = super.getConnection();
			
			String sql ="select count(*) from(select a.*, rownum rc from(select * from shopping_wares w where 1=1 ";
			if (word!=""&&word!=null) {
				sql+=" and w.wname like '%"+word+"%' or w.wtype like '%"+word+"%' ";
			}
			sql+=" ) a ) ";
			
			ps = con.prepareStatement(sql);
			rs=ps.executeQuery();
			while(rs.next()){
				count = rs.getLong(1);
			}
			rs.close();
			ps.close();
			con.close();
			return count;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}
	@Override
	public List<News> SelectNews(int id) {
		List<News> list = new ArrayList<News>();
		try {
			con = super.getConnection();
			String sql="select * from shopping_news where 1=1 ";
			if (id!=0) {
				sql+=" and nid = "+id;
			}
			ps=con.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()){
				News n = new News();
				n.setNid(rs.getInt(1));
				n.setNtitle(rs.getString(2));
				n.setNcontext(rs.getString(3));
				n.setNtime(rs.getString(4));
				list.add(n);
			}
			rs.close();
			ps.close();
			con.close();
			return list !=null?list:null;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
}